﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="BaseQueryUtility.cs" company="CodePlex">
//   Copyright © CodePlex 2012
// </copyright>
// <summary>
//   Defines the QueryUtility type.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace DataFramework.QueryUtilities
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.Diagnostics.CodeAnalysis;
    using System.Linq;
    using System.Text;

    using DataFramework.Attributes;
    using DataFramework.Factories;
    using DataFramework.Interfaces;
    using DataFramework.QueryConditions;

    /// <summary>
    /// The base query utility. This utility defines a large amount of virtual methods that can be overridden
    /// by more specific query utilities that target specific databases.
    /// </summary>
    public class BaseQueryUtility
    {
        /// <summary>
        /// Object to lock on for critical operations.
        /// </summary>
        internal static readonly object SyncRoot = new object();

        /// <summary>
        /// The base select string with replaceable elements.
        /// </summary>
        protected const string BaseSelectString = "SELECT {0} FROM {1}";

        /// <summary>
        /// The base insert string with replaceable elements.
        /// </summary>
        protected const string BaseInsertString = "INSERT INTO {0} ({1}) VALUES ({2})";

        /// <summary>
        /// The base update string with replaceable elements.
        /// </summary>
        protected const string BaseUpdateString = "UPDATE {0} SET {1} WHERE {2}";

        /// <summary>
        /// The base delete string with replaceable elements.
        /// </summary>
        protected const string BaseDeleteString = "DELETE FROM {0} WHERE {1}";

        /// <summary>
        /// Instance object for this singleton.
        /// </summary>
        private static volatile BaseQueryUtility instance;

        /// <summary>
        /// Initializes a new instance of the <see cref="BaseQueryUtility"/> class.
        /// </summary>
        [SuppressMessage("StyleCop.CSharp.DocumentationRules",
            "SA1642:ConstructorSummaryDocumentationMustBeginWithStandardText",
            Justification = "Reviewed. Suppression is OK here.")]
        protected BaseQueryUtility()
        {
            if (!DatabaseConfiguration.IsConfigured)
            {
                DatabaseConfiguration.Configure();
            }

            this.DatabaseHelper = DataProviderFactory.FetchConfiguredHelper();
        }

        /// <summary>
        /// Gets the instance.
        /// </summary>
        /// <value>The instance.</value>
        public static BaseQueryUtility Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (SyncRoot)
                    {
                        if (instance == null)
                        {
                            instance = new BaseQueryUtility();
                        }
                    }
                }

                return instance;
            }
        }

        /// <summary>
        /// Gets or sets the database helper.
        /// </summary>
        /// <value>The database helper.</value>
        internal IDatabaseHelper DatabaseHelper { get; set; }

        /// <summary>
        /// Fetches the correct data adapter based on the configured db type.
        /// </summary>
        /// <returns>a data adapter</returns>
        internal static IDbDataAdapter FetchCorrectDataAdapter()
        {
            IDbDataAdapter adapter;
            switch (DatabaseConfiguration.DbType)
            {
                case DatabaseType.OleDb:
                    adapter = new OleDbDataAdapter();
                    break;
                default:
                    throw new NotImplementedException("We don't know what type of data adapter is used!");
            }

            return adapter;
        }

        /// <summary>
        /// Fetches the correct command object based on the configured db type.
        /// </summary>
        /// <returns>a command object.</returns>
        internal IDbCommand FetchCorrectCommand()
        {
            IDbCommand command;
            switch (DatabaseConfiguration.DbType)
            {
                case DatabaseType.OleDb:
                    command = new OleDbCommand();
                    break;
                default:
                    throw new NotImplementedException("We don't know what type of command object is used!");
            }

            return command;
        }

        /// <summary>
        /// Builds the select statement for items.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="cmd">The CMD.</param>
        /// <param name="itemType">Type of the item.</param>
        /// <param name="conditions">The conditions.</param>
        internal virtual void BuildSelectStatementForItems(string tableName, IDbCommand cmd, Type itemType, IEnumerable<BaseQueryCondition> conditions)
        {
            var columnBuffer = new StringBuilder();
            var properties = itemType.GetProperties();
            foreach (var property in properties)
            {
                var columnMappings = property.GetCustomAttributes(true).OfType<ColumnMapping>().ToList();
                foreach (var mapping in columnMappings)
                {
                    columnBuffer.Append(columnBuffer.Length == 0 ? string.Format("{0}", mapping.ColumnName) : string.Format(", {0}", mapping.ColumnName));
                }
            }

            var buffer = new StringBuilder(string.Format(BaseSelectString, columnBuffer, tableName));
            if (conditions != null)
            {
                buffer.Append(this.BuildWhereClauseForSelect(cmd, itemType, conditions));
            }

            cmd.CommandText = buffer.ToString();
        }

        /// <summary>
        /// Builds the insert statement for item.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="cmd">The CMD.</param>
        internal virtual void BuildInsertStatementForItem(object item, string tableName, IDbCommand cmd)
        {
            var columnBuffer = new StringBuilder();
            var parameterBuffer = new StringBuilder();
            string columnName = null;
            var columnType = DbType.String;
            var includeInStatement = true;

            var properties = item.GetType().GetProperties();
            foreach (var property in properties)
            {
                foreach (var customAttribute in property.GetCustomAttributes(true).OfType<ColumnMapping>())
                {
                    columnName = customAttribute.ColumnName;
                    columnType = customAttribute.DbType;
                    includeInStatement = !customAttribute.IsKey || (customAttribute.IsKey && !customAttribute.IsAutoId);
                }

                var value = property.GetValue(item, null);
                value = this.DatabaseHelper.ConvertValueToDbSafeValue(value);

                if (!string.IsNullOrEmpty(columnName) && includeInStatement)
                {
                    columnBuffer.Append(columnBuffer.Length == 0 ? string.Format("{0}", columnName) : string.Format(", {0}", columnName));

                    parameterBuffer.Append(parameterBuffer.Length == 0 ? string.Format("{0}{1}", this.DatabaseHelper.QueryParameterPrefix, columnName) : string.Format(", {0}{1}", this.DatabaseHelper.QueryParameterPrefix, columnName));

                    cmd.Parameters.Add(this.DatabaseHelper.CreateParameter(columnName, columnType, value));
                }
            }

            cmd.CommandText = string.Format(BaseInsertString, tableName, columnBuffer, parameterBuffer);
        }

        /// <summary>
        /// Builds the update statement for item.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="cmd">The CMD.</param>
        internal virtual void BuildUpdateStatementForItem(object item, string tableName, IDbCommand cmd)
        {
            var setClauseBuffer = new StringBuilder();
            var whereClauseBuffer = new StringBuilder();
            var whereParameters = new List<IDataParameter>();

            var properties = item.GetType().GetProperties();
            foreach (var property in properties)
            {
                foreach (var customAttribute in property.GetCustomAttributes(true).OfType<ColumnMapping>())
                {
                    var value = property.GetValue(item, null);
                    value = this.DatabaseHelper.ConvertValueToDbSafeValue(value);

                    if (!string.IsNullOrEmpty(customAttribute.ColumnName))
                    {
                        if (!customAttribute.IsKey)
                        {
                            setClauseBuffer.Append(setClauseBuffer.Length == 0 ? string.Format("{1} = {0}{1}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName) : string.Format(", {1} = {0}{1}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName));
                            cmd.Parameters.Add(this.DatabaseHelper.CreateParameter(customAttribute.ColumnName, customAttribute.DbType, value));
                        }
                        else
                        {
                            whereClauseBuffer.Append(whereClauseBuffer.Length == 0 ? string.Format("{1} = {0}{1}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName) : string.Format("AND {1} = {0}{1}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName));
                        }

                        if (customAttribute.IsKey)
                        {
                            whereParameters.Add(this.DatabaseHelper.CreateParameter(customAttribute.ColumnName, customAttribute.DbType, value));
                        }
                    }
                }
            }

            cmd.CommandText = string.Format(BaseUpdateString, tableName, setClauseBuffer, whereClauseBuffer);

            foreach (var parm in whereParameters)
            {
                cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// Builds the delete statement for item.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="cmd">The CMD.</param>
        internal virtual void BuildDeleteStatementForItem(object item, string tableName, IDbCommand cmd)
        {
            var whereClauseBuffer = new StringBuilder();

            var properties = item.GetType().GetProperties();
            foreach (var property in properties)
            {
                foreach (var customAttribute in property.GetCustomAttributes(true).OfType<ColumnMapping>().Where(m => m.IsKey))
                {
                    var value = property.GetValue(item, null);
                    value = this.DatabaseHelper.ConvertValueToDbSafeValue(value);

                    if (!string.IsNullOrEmpty(customAttribute.ColumnName))
                    {
                        whereClauseBuffer.Append(whereClauseBuffer.Length == 0 ? string.Format("{1} = {0}{1}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName) : string.Format(", {1} = {0}{1}", this.DatabaseHelper.QueryParameterPrefix, customAttribute.ColumnName));

                        cmd.Parameters.Add(this.DatabaseHelper.CreateParameter(customAttribute.ColumnName, customAttribute.DbType, value));
                    }
                }
            }

            cmd.CommandText = string.Format(BaseDeleteString, tableName, whereClauseBuffer);
        }

        /// <summary>
        /// Safely reads the field specified from the provided data reader.
        /// </summary>
        /// <typeparam name="T">The type of the desired output.</typeparam>
        /// <param name="r">The reader.</param>
        /// <param name="attribute">The attribute.</param>
        /// <returns>An object of the specified type containing the value supplied from the data reader.</returns>
        internal virtual T SafeReadField<T>(IDataReader r, string attribute)
        {
            if (typeof(T).IsEnum)
            {
                return r[attribute] != DBNull.Value ? (T)r[attribute] : default(T);
            }

            return r[attribute] != DBNull.Value ? (T)Convert.ChangeType(r[attribute], typeof(T)) : default(T);
        }

        /// <summary>
        /// Fetches the operator SQL.
        /// </summary>
        /// <param name="cond">The cond.</param>
        /// <returns>The sql operator.</returns>
        protected static string FetchOperatorSql(BaseQueryCondition cond)
        {
            string operation;
            switch (cond.Type)
            {
                case ConditionType.Equal:
                    operation = "=";
                    break;
                case ConditionType.GreaterThanOrEqual:
                    operation = ">=";
                    break;
                case ConditionType.GreaterThan:
                    operation = ">";
                    break;
                case ConditionType.Is:
                    operation = "IS";
                    break;
                case ConditionType.LessThanOrEqual:
                    operation = "<=";
                    break;
                case ConditionType.LessThan:
                    operation = "<";
                    break;
                case ConditionType.NotEqual:
                    operation = "<>";
                    break;
                case ConditionType.And:
                    operation = "AND";
                    break;
                case ConditionType.Or:
                    operation = "OR";
                    break;
                default:
                    throw new NotImplementedException(string.Format("Condition {0} has not yet been implemented.", cond.Type));
            }

            return operation;
        }

        /// <summary>
        /// Builds the name of the unique parameter.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns>A unique parameter name</returns>
        protected static string BuildUniqueParameterName(IDbCommand cmd, string columnName)
        {
            if (!cmd.Parameters.Contains(columnName))
            {
                return columnName;
            }

            ulong x = 1;
            while (true)
            {
                if (!cmd.Parameters.Contains(columnName + x))
                {
                    return columnName + x;
                }

                x++;
            }
        }

        /// <summary>
        /// Builds the where clause.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <param name="itemType">Type of the item.</param>
        /// <param name="conditions">The conditions.</param>
        /// <returns>A formatted where clause</returns>
        protected virtual string BuildWhereClauseForSelect(IDbCommand cmd, Type itemType, IEnumerable<BaseQueryCondition> conditions)
        {
            var buffer = new StringBuilder(" WHERE ");
            this.IterateConditions(cmd, itemType, conditions, buffer);

            return buffer.ToString();
        }

        /// <summary>
        /// Iterates the conditions.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <param name="itemType">Type of the item.</param>
        /// <param name="conditions">The conditions.</param>
        /// <param name="buffer">The buffer.</param>
        protected virtual void IterateConditions(IDbCommand cmd, Type itemType, IEnumerable<BaseQueryCondition> conditions, StringBuilder buffer)
        {
            // var multipleConditions = conditions.Count() > 1;
            var firstPiece = true;

            // MAYBE: Order conditions? 
            foreach (var condition in conditions) 
            {
                var known = false;
                if (condition is QueryCondition)
                {
                    var cond = condition as QueryCondition;
                    known = true;
                    this.BuildCondition(cmd, itemType, !firstPiece, cond, buffer);
                }

                if (condition is CompositeQueryCondition)
                {
                    var cond = condition as CompositeQueryCondition;
                    known = true;
                    this.BuildCompositeCondition(cmd, itemType, cond, buffer);
                }

                firstPiece = false;

                if (!known)
                {
                    throw new NotImplementedException("QueryCondition not yet implemented");
                }
            }
        }

        /// <summary>
        /// Builds the composite condition.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <param name="itemType">Type of the item.</param>
        /// <param name="cond">The cond.</param>
        /// <param name="buffer">The buffer.</param>
        protected virtual void BuildCompositeCondition(IDbCommand cmd, Type itemType, CompositeQueryCondition cond, StringBuilder buffer)
        {
            buffer.Append("(");
            this.IterateConditions(cmd, itemType, cond.ConditionSet1, buffer);
            buffer.Append(") ");
            buffer.Append(FetchOperatorSql(cond));
            buffer.Append(" (");
            this.IterateConditions(cmd, itemType, cond.ConditionSet2, buffer);
            buffer.Append(")");
        }

        /// <summary>
        /// Builds the condition.
        /// </summary>
        /// <param name="cmd">The CMD.</param>
        /// <param name="itemType">Type of the item.</param>
        /// <param name="includeAnd">if set to <c>true</c> [include and].</param>
        /// <param name="condition">The condition.</param>
        /// <param name="buffer">The buffer.</param>
        protected virtual void BuildCondition(IDbCommand cmd, Type itemType, bool includeAnd, QueryCondition condition, StringBuilder buffer)
        {
            var property = itemType.GetProperties().FirstOrDefault(p => p.Name == condition.PropertyName);
            if (property != null)
            {
                var mapping = property.GetCustomAttributes(true).OfType<ColumnMapping>().FirstOrDefault();

                if (includeAnd)
                {
                    buffer.Append(" AND ");
                }

                if (mapping != null)
                {
                    var parameterName = BuildUniqueParameterName(cmd, mapping.ColumnName);
                    this.BuildConditionSection(mapping, parameterName, buffer, condition);
                    cmd.Parameters.Add(this.DatabaseHelper.CreateParameter(parameterName, mapping.DbType, condition.PropertyValue));
                }
                else
                {
                    throw new NullReferenceException("ColumnMapping could not be found.");
                }
            }
            else
            {
                throw new NullReferenceException("Specified property name could not be found.");
            }
        }

        /// <summary>
        /// Builds the condition section.
        /// </summary>
        /// <param name="mapping">The mapping.</param>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <param name="buffer">The buffer.</param>
        /// <param name="cond">The cond.</param>
        protected virtual void BuildConditionSection(ColumnMapping mapping, string parameterName, StringBuilder buffer, QueryCondition cond)
        {
            var operation = FetchOperatorSql(cond);
            buffer.Append(string.Format("{0} {1} {2}{3}", mapping.ColumnName, operation, this.DatabaseHelper.QueryParameterPrefix, parameterName));
        }
    }
}
